package com.hsnn.medstgmini.base.dict.dao.impl;

import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicDrugformDao;
import com.hsnn.medstgmini.base.dict.model.DicDrugform;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 基础库 数据字典 剂型字典 接口 实现类
 * @author 韩守松
 * @date 2015年6月24日
 */
@Repository
public class DicDrugformDaoImpl extends BaseHibernateDAO implements
		DicDrugformDao {
	private static final Logger log = Logger
			.getLogger(DicDrugformDaoImpl.class);
	@Autowired
	private HibernateSqlUtil dao;
	/**
	 * 基础库 数据字典 剂型字典 保存（增加和修改）
	 * 
	 * @param dicDrugform
	 */
	@Override
	public boolean addAndEdit(DicDrugform dicDrugform) {
			getSession().saveOrUpdate(dicDrugform);
			getSession().flush();
			return true;
	}

	/**
	 * 基础库 数据字典 剂型字典 查询
	 * 
	 * @param dicDrugform
	 */
	@Override
	public Pagination queryDicDrugformList(Pagination page,
			DicDrugform dicDrugform) {

		String formCode = dicDrugform.getFormCode();
		String formName = dicDrugform.getFormName();
		String status = null;
		if (dicDrugform.getStatus() != null ) {
			 status = dicDrugform.getStatus()+"";
		}
		StringBuffer sql = new StringBuffer();
		sql.append("	      FROM                                              ")
			.append("		(SELECT b.form_id,a.form_code father_form_code,     ")
			.append("	    a.form_name father_form_name,b.form_code,           ")
			.append("        b.form_name,b.status FROM dic_drugform a           ")
			.append("	    INNER JOIN dic_drugform b                           ")
			.append("		 ON a.form_id=b.father_id                           ")
			.append("		UNION                                               ")
			.append("		SELECT form_id,form_code father_form_code,          ")
			.append("       form_name father_form_name,NULL,NULL,STATUS         ")
			.append("       FROM dic_drugform WHERE father_id='0')t where 1=1   ");
		
		if (StringUtils.isNotBlank(formCode)) {
			sql.append(" and (t.form_code = :formCode or t.father_form_code =:formCode) ");
		}
		if (StringUtils.isNotBlank(formName)) {
			sql.append(" and (t.form_name like :formName or t.father_form_name like :formName)  ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and (t.status = :status) ");
		}
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		hql.append("select * ")
			.append(sql.toString());
		hqlCount.append("select count(*) ")
				.append(sql.toString());
		hql.append(" order by t.father_form_code asc,t.form_code asc ");
		Query query = getSession().createSQLQuery(hql.toString());
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		if (StringUtils.isNotBlank(formCode)) {
			query.setParameter("formCode",  formCode );
			queryCount.setParameter("formCode", formCode);
		}
		if (StringUtils.isNotBlank(formName)) {
			query.setParameter("formName", "%" + formName + "%");
			queryCount.setParameter("formName", "%" + formName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", status);
			queryCount.setParameter("status", status);
		}
		//优化获取返回集合的条数yxc
		page.setRecords(Long.parseLong(queryCount.uniqueResult().toString()));
		//dicDrugform.setCount(Long.valueOf(queryCount.list().get(0)+""));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}


	/**
	 * 基础库 数据字典 剂型字典 根据id查询数据
	 * 
	 * @param Id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugform> queryDicDrugformById(String Id) {
		StringBuffer hql = new StringBuffer();
		hql.append("from DicDrugform where form_id = ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, Id);
		return query.list();
	}

	
	/**
	 * 
	 *@category 启用和停用
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void changeStatus(DicDrugform dicDrugform) {
		StringBuffer sql = new StringBuffer();
		String formId = dicDrugform.getFormId();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if(0 == dicDrugform.getStatus()){
			sql.append(" update dic_drugform  set ")
			   .append(" status=:status,upd_datetime= SYSDATE(),upd_user=:updUser ")
			   .append(" where form_id= :formId ");
				params.put("formId", formId);
		}else{
			sql.append("update dic_drugform  set ")
			  .append(" status= :status ,upd_datetime= SYSDATE() ,upd_user= :updUser  ")
			  .append(" where form_id= :formId ");
				params.put("formId", formId);
		}
		if("0".equals(dicDrugform.getStatus()+"")){
			sql.append(" or father_id=:formId ");
		}
		params.put("status", dicDrugform.getStatus());
		params.put("updUser", dicDrugform.getUpdUser());
		dao.executeSql(sql.toString(), params);
		String msg = "";
		if (1 == dicDrugform.getStatus()){
			msg += "由停用变为启用";
		}
		else if (0 == dicDrugform.getStatus()){
			msg += "由启用变为停用";
		}
		/*update.executeUpdate();
		session.flush();*/
		writeLog(dicDrugform, msg);
	}
	/**
	 * 
	 *@category 写日志
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void writeLog(DicDrugform dicDrugform, String msg){
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugform_log(log_id,form_id,form_code,form_name,  ")
		   .append("father_id,status,upd_datetime,upd_user,sort_id,detail) select ")
		   .append(" UUID(),t.*,:msg from dic_drugform t where t.form_id= :formId ");
		if("0".equals(dicDrugform.getStatus()+"")){
			sql.append(" or t.father_id= :formId or t.father_id in (select a.form_id from dic_drugform a where a.father_id = :formId ) ");
		}
		Query query=session.createSQLQuery(sql.toString());
		query.setString("formId",dicDrugform.getFormId());
		query.setString("msg", msg);
		query.executeUpdate();
		session.flush();
		
	}
	
	@Override
	public Pagination initEdit(Pagination page){
		String formId = page.getConditions().get("formId");
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT b.form_code fatherFormCode,b.form_name fatherFormName,a.form_code formCode,a.form_name  formName FROM dic_drugform a ")
		   .append(" LEFT JOIN dic_drugform b ON a.father_id = b.form_id WHERE a.form_id =:formId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("formId",formId);
		page.setRows(query.uniqueResult());
		return page;
	}
	/**
	 * 剂型编辑
	 */
	@Override
	public void updateForm(Pagination page) {
		String formId = page.getConditions().get("formId");
		String msg = compareData(formId,page);
		if (StringUtils.isNotBlank(msg)){
		String formCode = page.getConditions().get("formCode");
		String formName = page.getConditions().get("formName");
		String updUser = page.getConditions().get("updUser");
		StringBuffer sql = new StringBuffer();
		sql.append(" update dic_drugform set form_code=:formCode, ")
		   .append("form_name=:formName,upd_datetime=:updDatetime,")
		   .append(" upd_user=:updUser where form_id=:formId      ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("formId", formId);
		params.put("formName",formName );
		params.put("formCode",formCode );
		params.put("updUser",updUser );
		params.put("updDatetime", new Date());
		dao.executeSql(sql.toString(), params);
		String logsql = "insert into dic_drugform_log select UUID(),a.form_id,a.form_code,a.form_name,a.father_id,a.status,a.upd_datetime,a.upd_user,a.sort_id,:msg from dic_drugform a where a.form_id=:formId";
		params.put("msg",msg );
		params.remove("formCode");
		params.remove("formName");
		params.remove("updUser");
		params.remove("updDatetime");
		dao.executeSql(logsql, params);
		}
	}
	
	private String compareData(String id, Pagination page) {
		String sql = "select * from dic_drugform where form_id = :formId";
		Map<String, Object> params = new HashMap<String, Object>();
		String formId = page.getConditions().get("formId");
		params.put("formId", formId);
		@SuppressWarnings("unchecked")
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
		if (null != list && list.size() > 0){
			Map<String, Object> map = list.get(0);
			String formCode = page.getConditions().get("formCode");
			String formName = page.getConditions().get("formName");
			StringBuffer msg = new StringBuffer();
			if (! formCode.equals(map.get("form_code").toString())){
				msg.append("剂型编码由")
				.append(map.get("form_code").toString())
				.append("变为")
				.append(formCode)
				.append("<br/>");
			}
			if (! formName.equals(map.get("form_name").toString())){
				msg.append("剂型名称由")
				.append(map.get("form_name").toString())
				.append("变为")
				.append(formName)
				.append("<br/>");
			}
			return msg.toString();
		}
		return null;
	}

	/**
	 * 剂型新增
	 */
	@Override
	public void addForm(Pagination page) {
		String formId = page.getConditions().get("formAddId");
		String formCode = page.getConditions().get("formCode");
		String formName = page.getConditions().get("formName");
		String updUser = page.getConditions().get("updUser");
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugform(form_id,form_code,form_name,father_id,  ")
		   .append("status,upd_datetime,upd_user) values ( ")
		   .append(":formId,:formCode,:formName,:fatherId,1,:updDatetime,:updUser) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("formId", formId);
		params.put("formCode",formCode );
		params.put("formName",formName );
		params.put("updUser",updUser );
		params.put("updDatetime", new Date());
		params.put("fatherId",page.getConditions().get("fatherId"));
		dao.executeSql(sql.toString(), params);
		String logsql = "insert into dic_drugform_log select UUID(),a.form_id,a.form_code,a.form_name,a.father_id,a.status,a.upd_datetime,a.upd_user,a.sort_id,:msg from dic_drugform a where a.form_id=:formId";
		params.put("msg", "新增剂型");
		params.remove("formCode");
		params.remove("formName");
		params.remove("updUser");
		params.remove("updDatetime");
		params.remove("fatherId");
		dao.executeSql(logsql, params);
	}
	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugform> getFormBy(String key,String value,String formId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from dic_drugform where ")
		   .append(key)
		   .append(" = :value and  form_id <> :formId ");
		if("form_name".equals(key)){
			//剂型名称可以和自己的一级或二级相同
			sql.append(" and form_id not in (select form_id from dic_drugform where father_id = :formId ) ");
			sql.append(" and form_id not in (select father_id from dic_drugform where form_id = :formId ) ");
		}
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter("value", value);
		query.setParameter("formId",formId);
		return query.list();
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("剂型");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        
        HSSFCell cell = null;
        String[] headers = {"一级编码","一级名称","二级编码","二级名称","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getFormNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/drugForm.xls");  
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}
	
	@SuppressWarnings("unchecked")
	private List<Object> getFormNoPage(Pagination page){
		String formCode = page.getConditions().get("formCode");
		String formName = page.getConditions().get("formName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer();
		sql.append("	     select *     FROM                                  ")
			.append("		(SELECT a.form_code father_form_code,     ")
			.append("	    a.form_name father_form_name,b.form_code,           ")
			.append("        b.form_name,case b.status when 0 then '停用' else '启用' end status FROM dic_drugform a           ")
			.append("	    INNER JOIN dic_drugform b                           ")
			.append("		 ON a.form_id=b.father_id                           ")
			.append("		UNION                                               ")
			.append("		SELECT form_code father_form_code,          ")
			.append("       form_name father_form_name,NULL,NULL,case STATUS when 0 then '停用' else '启用' end   status      ")
			.append("       FROM dic_drugform WHERE father_id='0')t where 1=1   ");
		    
		if (StringUtils.isNotBlank(formCode)) {
			sql.append(" and (t.form_code = :formCode or t.father_form_code =:formCode) ");
		}
		if (StringUtils.isNotBlank(formName)) {
			sql.append(" and (t.form_name like :formName or t.father_form_name like :formName)  ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and (t.status = :status) ");
		}
		sql.append("       order by t.father_form_code asc,t.form_code asc     ");
		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(formCode)) {
			query.setParameter("formCode",  formCode );
		}
		if (StringUtils.isNotBlank(formName)) {
			query.setParameter("formName", "%" + formName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", "0".equals(status)?"停用":"启用");
		}
		return query.list();
	}
}
